Mega Code Archive
Analytical Functions
1) (expr1, expr2)
2) A seven-day MAX and MIN on Tuesdays
3) Accessing a Range of Cells Using BETWEEN and AND
4) Accessing Cells Using a FOR Loop
5) Add an analytic order by clause
6) Adding an Analytical Function to a Query that Contains a Join (and Other WHERE Conditions)
7) Adding an Analytical Function to the GROUP BY with ORDER BY Version
8) An Example of the MODEL Clause
9) An Example Query that Illustrates the Use of GROUPING_ID()
10) Analytic order-by clause
11) ANY with positional notation
12) Average 5 before, after
13) Avg over and avg over order by
14) Avg over order by range
15) Avg over range between
16) Avg over ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING)
17) Avg over, first_value over
18) Browse Products with ranking function
19) Changing the Final Ordering after Having Added an Analytical Function
20) Changing the Position of Columns Passed to ROLLUP
21) Column alias for the analytical function is used in the final ORDER BY
22) Combine analytical function with count()
23) Compare rank() and dense_rank()
24) Compute
25) COMPUTE sum max min
26) Connect by prior
27) Control whether nulls are the highest or lowest in a group using NULLS FIRST
28) Control whether nulls are the highest or lowest in a group using NULLS LAST
29) Corr
30) Count employees, group by CUBE(department no, job title)
31) Count employees, group by ROLLUP(department no, job title)
32) Count() over partition
33) Count() over partition by, order by and range unbounded preceding
34) Count() over partition by, order by, range unbounded preceding
35) Count() over partition, order by, range unbounded preceding
36) Count() over partition, order by, range unbounded preceding for employee table
37) COVAR_POP(expr1, expr2)
38) COVAR_SAMP(expr1, expr2)
39) Create a view based on row_number function
40) CUBE clause
41) Cube implies reducing tables by rolling up different columns (dimensions)
42) CUME_DIST()
43) Cume_dist over
44) Cumulative total_order_price for all orders
45) Deal with Null in dense_rank
46) Deal with Null in Rank
47) Decode dense_rank
48) Decode the result from row_number over, partition by, order by
49) Demo the effect of WHERE on CONNECT BY
50) Demonstrate a partial rollup
51) Demonstrate GROUPING in the HAVING clause
52) Demonstrate Partial CUBE operation
53) DENSE_RANK function
54) Dense_rank over partition by
55) Dense_rank() over ( partition by deptno order by sal desc )
56) Dense_rank() over (order by comm desc nulls last)
57) Dense_rank() over partition by, order by
58) DENSE_RANK() with NULLS FIRST
59) DENSE_RANK() with NULLS LAST
60) DENSE_RANK()rank items, leaves no gaps when there is a tie
61) Display a cumulative total_order_price for all orders(Partition by the truncated order_date
62) Display cumulative total_order_price and cumulative by customer
63) Displaying a Running Total Using SUM as an Analytical Function
64) Eliminate duplicate rows using a HAVING clause that only allows rows whose GROUP_ID() is 0
65) Employee salary report with avg salary for the previous 12 months
66) FIRST and LAST functions to get the first and last values in an ordered group
67) FIRST_VALUE and LAST_VALUE
68) FIRST_VALUE() get the first rows in a window
69) First_value order by range between preceding and following
70) First_value over order by
71) First_value over order by range
72) First_value over order by rows
73) First_value over partition
74) Get median salary
75) Get Products
76) Getting the Current Value of a Dimension Using CURRENTV()
77) Group by cube
78) Group by CUBE(department no, job title)
79) Group by rollup
80) Group by ROLLUP(department no, job title)
81) GROUPING SETS
82) GROUPING() returns 0 for the rows that have non-null values, and 1 for the last row that has a null value
83) GROUPING(x)
84) Grouping with Cube
85) Having grouping(c class_desc) = 1
86) If more analytical functions are added, yet more sorting may result
87) If NULLS LAST is left out of the final ORDER BY, the effect will be lost
88) If the statement were without NULLS LAST, the values of the NVLd nulls occur first
89) Include a final ordering of the result set with an ORDER BY at the end of the query
90) IS ANY with symbolic notation
91) Is our average total_order_price increasing or decreasing
92) IS PRESENT returns true if the row specified by the cell reference existed prior to the execution of the MODEL clause
93) Lag over
94) Lag over order
95) Lag salary over, lead salary over
96) LAG() and LEAD()
97) LAG, LEAD
98) Last_value over range
99) Last_value over range between
100) LAST_VALUE()
101) Lead over order by
102) Lead over partition
103) MEDIAN(expr1)
104) More Than One Analytical Function May Be Used in a Single Statement
105) More Than One Analytical Function May Be Used in a Single Statement ROW_NUMBER(), RANK(), DENSE_RANK()
106) Multiple COMPUTEs are also allowable
107) Notice that the PORTION column adds up to 100%
108) NTILE(2) OVER (ORDER BY SUM(amount) DESC) AS ntile
109) NTILE(4) function is used for that ordering
110) NTILE(4) in reverse order
111) NTILE(buckets) to calculate n-tiles
112) Ntile and row_number
113) Ntile as quartile
114) NTile with NULLS LAST
115) Nulls could also be handled with a default value using the NVL function in the analytical function
116) NULLS FIRST
117) NULLS LAST
118) One useful application of GROUPING_ID() is to filter rows using a HAVING clause
119) Open a cursor created by using row_number function
120) Order by nulls last
121) PARTITION BY
122) PARTITION BY (JOB title) and right outer join
123) Partition clause
124) Partition Window
125) Partitioning with PARTITION_BY
126) Passing Multiple Columns to ROLLUP
127) Percent_rank (PR) = (Rank-1)(Number of rows-1)
128) PERCENT_RANK()
129) PERCENTILE_DISC(x) examines the cumulative distribution values in each group until it finds one that is greater than
130) Performing a Centered Average
131) Performing a Cumulative Sum
132) Performing a Moving Average
133) Portion amounts add to 1 000 in each region
134) PRESENTV(cell, expr1, expr2)
135) Range unbounded preceding
136) Rank and dense_rank over partition
137) Rank and dense_rank with group by
138) Rank and percent_rank
139) Rank row without analytical functions
140) Rank with order by clause
141) RANK()
142) Rank() over (partition by deptno order by sal desc )
143) Rank() over partition
144) Rank() with null values
145) RANK() with NULLS FIRST
146) Rank() with nulls last
147) RANK() with NULLS LAST demo
148) Rank, Percent_Rank, and Cume_Dist
149) Rank, Percent_Rank, and Cume_Dist, NTILE
150) RATIO_TO_REPORT can easily be partioned as well
151) RATIO_TO_REPORT Demo
152) RATIO_TO_REPORT()
153) RATIO_TO_REPORT, break on and compute
154) REGR_AVGX(expr1, expr2)
155) REGR_AVGX(y, x)
156) REGR_AVGY(expr1, expr2)
157) REGR_AVGY(y, x) Returns the average of y after eliminating x and y pairs where either x or y is null
158) REGR_COUNT(expr1, expr2)
159) REGR_COUNT(y, x)
160) REGR_INTERCEPT(expr1, expr2)
161) REGR_INTERCEPT(y, x) Returns the intercept on the y-axis of the regression line
162) REGR_R2(expr1, expr2)
163) REGR_R2(y, x) Returns the coefficient of determination, or R-squared, of the regression line
164) REGR_SLOPE(expr1, expr2)
165) REGR_SLOPE(y, x) Returns the slope of the regression line
166) REGR_SXX(y, x) Returns REG_COUNT(y, x) VAR_POP(x)
167) REGR_SXY(expr1, expr2)
168) REGR_SXY(y, x) Returns REG_COUNT(y, x) COVAR_POP(y, x)
169) REGR_SYY(expr1, expr2)
170) REGR_SYY(y, x) Returns REG_COUNT(y, x) VAR_POP(y)
171) Reporting on a Sum
172) Rollup
173) ROLLUP and RANK() to get the sales rankings by product type ID
174) Rollup function in group by clause
175) ROW_NUMBER function with an ordering on salary in descending order
176) Row_number over partition by and order by
177) ROW_NUMBER()
178) Row_number() over (partition by deptno order)
179) ROW_NUMBER() with order in descending order
180) Row-ordering is done first and then the moving average
181) RULES UPDATE, which specifies that if the cell doesnt exist, dont create a new row
182) Salary report with cumulative salary, using analytic function SUM
183) Salary report with cumulative salary, without using analytic function
184) See the difference between a persons salary and the average for his or her region
185) Select percentile_cont(0 5) within group (order by salary desc )
186) SPREADSHEET PARTITION BY
187) Start connect by
188) START WITH mgr is null CONNECT BY NOCYCLE PRIOR empno = mgr
189) Start with and connect by
190) Start with column is null
191) STATS_MODE(expr)
192) STATS_WSR_TEST(expr1, expr2, ONE_SIDED_SIG)
193) STATS_WSR_TEST(expr1, expr2, TWO_SIDED_SIG)
194) STATS_WSR_TEST(y, x, STATISTIC)
195) STDDEV([DISTINCT ALL] value) [OVER (analytic_clause)]
196) STDDEV(x)
197) STDDEV_POP(expr) [OVER(analytic_clause)]
198) STDDEV_SAMP(expr) [OVER(analytic_clause)]
199) Sum over (nothing)
200) Sum over and order by
201) Sum over and row_number() over
202) Sum over order by
203) Sum over partition
204) Sum over partition and sum over order as running total
205) Sum over partition by and order by
206) Sum over partition by, order by
207) Sum over partition demo
208) Sum over range
209) Sum salary over PARTITION BY
210) SUM(y) OVER(ORDER BY x ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING)
211) The default is NULLS FIRST
212) The direct application of a WHERE clause in the query is not allowed
213) The query divides the current months sales amount by the previous months sales amount
214) The ranking as opposed to a row-numbering problem (the problem of ties)
215) The ROLLUP clause extends GROUP BY to return a row containing a subtotal for each group along with a total for all gr
216) The row comparison function can also be partitioned as with other aggregates
217) The Row Comparison Functions LEAD and LAG
218) The sequence of the columns passed into Cube function
219) The top five salaries
220) The use of ROWS UNBOUNDED PRECEDING to implicitly indicate the end of the window is
221) The WHERE is applied before the RANK()
222) To get a clearer picture of the NTILE function
223) To see how the moving average window can expand
224) Top with partition
225) UNBOUNDED FOLLOWING is used for the end of the window
226) Use an analytical function in a WHERE clause
227) Use CUBE and RANK() to get all rankings of sales by product type ID and employee ID
228) Use DENSE_RANK() to get the top rank
229) Use GROUPING SETS and RANK() to get just the sales amount subtotal rankings
230) Use GROUPING SETS to produce just a subset of CUBE
231) Use lag over and lead over in inner statement
232) Use lag over in inner query
233) Use multiple analytical functions
234) Use over partition in subquery
235) Use partitioning in the OVER clause of the aggregate-analytical function like this
236) Use rank and dense_rank in subquery
237) Use symbolic notation to explicitly indicate the meaning of the dimensions
238) Use the COUNT aggregate analytical function to show how many rows are included in each window
239) Uses dates and logical offset of seven days preceding
240) Using Analytic Functions
241) Using Analytic Functions AVG(Mark) OVER (PARTITION BY StudentID ORDER BY StudentID, Mark)
242) Using AVG with ROLLUP
243) Using DECODE() and GROUPING() to Convert Multiple Column Values
244) Using DECODE() to Convert the Returned Value from GROUPING()
245) Using GROUPING() with CUBE
246) Using IGNORE NAV and KEEP NAV
247) VAR_POP(expr)
248) VAR_SAMP(expr)
249) VARIANCE()
250) VARIANCE([DISTINCT ALL] expr)
251) What fraction of the total salary budget goes to which individual
252) When RANK() is added to the statement, the RANK function is applied last, just before the ordering
253) With ROLLUP and ROW_NUMBER added
254) You can use the GROUP_ID() function to remove duplicate rows returned by a GROUP BY clause